Google App Scripts

Google App Script allows extensible functions to be created to work with G Suite products such as Docs, Sheets, Slides and Forms. It uses JavaScript as the language and can do things like:

Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms.
Write custom functions for Google Sheets.
Publish web apps — either standalone or embedded in Google Sites.
Interact with other Google services, including AdSense, Analytics, Calendar, Drive, Gmail, and Maps.
Build add-ons to extend Google Docs, Sheets, Slides, and Forms, and publish them to the Add-on store.
Convert an Android app into an Android add-on so that it can exchange data with a user’s Google Doc or Sheet on a mobile device.
Streamline Hangouts Chat workflows by building a chat bot.

Google App Script allows one or many ‘applications’ to be created. This is done at: script.google.com

Applications can be created and run within the Google App Script service. It is also possible to run Google App Script outside of the service through REST API. This allows external applications (outside of Google G Suite) to access G Suite applications and data. More information about this can be found here:

https://developers.google.com/apps-script/api/concepts/

When working with Google App Script, there are 3 different types of scripts that can be created. These are:

Standalone – any script that is not bound to a G Suite product such as Sheets, Docs, Slides or Forms. These scripts in Google Drive and can run alone without another product.
Bounded – these are scripts bound to a G Suite product such as Sheets, Docs, or Slides. These bounded scripts do not appear in Google Drive but instead inside the G Suite product file that it is bounded to. They function only within that file. These are also considered as unpublished add-ons (which is a feature of G Suite products).
Web Apps – these are scripts that function as a web application and can interact with G Suite products. Web apps can be full web applications serving HTML/CSS/JS or they can also perform as REST API endpoints (but only serve HTTP GET and POST). There are two requirements when creating App Scripts that are web apps:
1. It must contain a doGet(e) or doPost(e) function
2. It must return an HTMLOutput service object or a TextOutput content service object

More information about the content types that App Script can handle can be found here:

https://developers.google.com/apps-script/reference/content/

With the use of App Scripts we can interface with all of G Suite products. The full list is below:

Calendar
Contacts
Document
Drive
Forms
Gmail
Groups
Language
Maps
Sites
Slides
Spreadsheet

Details of the App Script services available for each of these products can be found here:

https://developers.google.com/apps-script/reference/

App Script Web Apps

App Script web apps can serve HTML/CSS/JS as a full web page or act as REST API endpoints. It also supports request parameters, which are listed here:

https://developers.google.com/apps-script/guides/web

Web apps need to be deployed through App Scripts. The service supports versioning and access control – all of which is configured during deployment. Note that if the web app is accessing other G Suite products/files, the permissions needs to be configured appropriately.

Note that when creating web apps with multiple files, we need to create a separate function to include them into the main index.html (entry point) file. Google has a best practices page explaining this here:

https://developers.google.com/apps-script/guides/html/best-practices

A sample of that code can be see below.

Code.gs

function doGet(request) {
  return HtmlService.createTemplateFromFile('Page')
      .evaluate();
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

function doSomething(data) {
 Logger.log('Something was done!');
 return 9999;
}

Page.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Stylesheet'); ?>
  </head>
  <body>
    <h1>Welcome</h1>
    <p>Please enjoy this helpful script.</p>
    <button onclick="run()">List Courses</button>
    <?!= include('JavaScript'); ?>
    <script>
    function run() {
     google.script.run
     .withSuccessHandler(function(response) {
        console.log(response);
     })
     .withFailureHandler(function(error) {
        console.error(error):
     })
    .helloworld();
    }
    </script>
  </body>
</html>

Note that in the above example for the doGet(request), that request object contains information about the request including any query parameters. It can look something like:

{
  "queryString": "username=jsmith&age=21",
  "parameter": {
    "username": "jsmith",
    "age": "21"
  },
  "contextPath": "",
  "parameters": {
    "username": [
      "jsmith"
    ],
    "age": [
      "21"
    ]
  },
  "contentLength": -1
}

Warning: The following parameter names are reserved by the system and shouldn’t be used in URL parameters or POST bodies:

c
sid

Using these parameters may result in an HTTP 405 response with the error message “Sorry, the file you have requested does not exist.” If possible, update your script to use different parameter names.

Google Banner

The GAS web apps have one annoyance when deployed. It contains a warning banner that cannot be removed. When looking at the source, the web app actually runs inside an IFrame. More information is posted here on stackoverflow:

https://stackoverflow.com/questions/44970940/how-to-remove-warning-this-application-was-created-by-another-user-not-by-goog

External API Calls

From the google app script server side we can call external APIs using the UrlFetchApp method. Details about using this can be found here:

https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app

In sample project below we call our REST API through this method to avoid CORS issues when calling the REST API directly from the client web page.

CORS

Google servers do not provide CORS header content. Therefore when working with REST API via Google App Scripts, we need to handle the cross origin request headers. This is explained here:

https://developers.google.com/apps-script/guides/content

Note that when making multiple web apps in Google App Script, each application is designated with a unique identifier which is used in the URL. Though it may look like the URL origin is script.google.com, all web apps run in an IFrame where the content is loaded from another location. That url is something like\

https://[identifier]-script.googleuseraccount.com/…

As such, even when trying to do an API call back to the original script.google.com… URL will throw a CORS error. I’ve found that for GET requests, the requests seem to go through. Others online report problems with this though, and it might be tied to the type of G Suite license they are on (Business, Non-Profit, etc). For my Non-Profit and personal account, GET requests went through but POST did not.

An easy fix for performing GET around CORS is to use JSONP instead of XHR/XMLHttpRequest. However, there is no easy way around the POST problem. In the end – as shown in my sample project below – I was unable to perform a POST request and resorted back to using the Google template code.

Sample Project

I have a sample project where we list some people names and let the user select them. When they click SAVE, the list of people selected will be stored on to a Google Sheet. The source can be found here:

https://github.com/revivepres/chdir

Initially I approached this project thinking I would be able to use REST API calls from the client side. I didnt know that the web app actually runs within an IFrame and therefore a different domain than the originally served script.google.com domain. We do not have any CORS control in Google App Scripts and therefore the REST API option was not possible. The code below shows how I initially set this up.

function doGet() {
    var ss = SpreadsheetApp.openById("12345abcde");
    var sheet = ss.getSheetByName("Roster");
    var range = sheet.getDataRange();
    var values = range.getValues();
    var headers = values.shift().map(function (header) { return normalizeName(header); })
  
    // Ignore blank rows
    var filtered = values.filter(function (row) {
      if (row[3] && row[4]) { return true; }
      return false;
    });
  
    // Map rows to JSON
    var data = filtered.map(function (row) {
      var reducedRow = row.reduce(function (accum, value, index, arr) {
        accum[headers[index]] = value;  
        if (index >= 4) {
          arr.splice(1); // break iteration
        }
        return accum;
      }, {});
      return reducedRow;
    });
  
    return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
  }

  function normalizeName(name) {
      return name.toLowerCase().replace(/ /g, '').replace(/[^a-z0-9_-]/g, '');
  }

  function doPost(e) {
    if (e.postData.contents) {
      var data = JSON.parse(e.postData.contents);
      var week = data.week;
      var guests = data.guests;
      var total = data.total;
      var members = data.members;
     
      var ss = SpreadsheetApp.openById("abcdef12345");
      var sheet = ss.getSheetByName('Weekly');
      var range = sheet.getRange('A:A');
      var values = range.getValues();
      
      // clear out the sheet for given week
      for (var i = values.length-1; i >= 0; i--) {
        var value = new Date(values[0,i]);
        if (!isNaN(value) && week) {       
          var day = ("0" + value.getDate()).slice(-2);
          var month = ("0" + (value.getMonth() + 1)).slice(-2);
          var date = value.getFullYear() + "-" + (month) + "-" + (day);
          if(date == week) {
            sheet.deleteRow(i+1);
          }        
        }
      }
      
      // Add guest and member rows
      var values = [];
      for (var g = 0; g < guests; g++) {
        values.push([week, 0, 'Guest']);
      }
      if (members) {
        members.forEach(function(member) {
          values.push([week, member.id, member.name]);
        });
      }
      
      // Append to sheet
      sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
      return ContentService.createTextOutput('Successfully added ' + values.length + ' records');
    }  
    return ContentService.createTextOutput('ERROR - no data received in content body');
  }

As mentioned in the CORS section above, the GET request worked fine for me but the POST did not. I couldn’t find an easy way around it so ended up resorting back to using the google.script.run approach from the HTML side. This meant I no longer needed the doPost() and instead replaced it with this save() method. Note that this method still takes a JSON ‘data’ object as input. The method returns a status text message.

function save(data) {
  if (data && data.members.length > 0) {
    var week = data.week;
    var guests = data.guests;
    var total = data.total;
    var members = data.members;

    var ss = SpreadsheetApp.openById("spreadsheetid");
    var sheet = ss.getSheetByName("sheetname");
    var range = sheet.getRange("A:A");
    var values = range.getValues();

    // Add guest and member rows
    var attendees = [];
    if (members) {
      members.forEach(function (member) {
        attendees.push([week, member.id, member.name]);
      });
    }

    // Append to sheet
    sheet.getRange(sheet.getLastRow() + 1, 1, attendees.length, attendees[0].length).setValues(attendees);
    return "Saved " + total + " attendees for " + week;
  }
  return "ERROR! Nothing to save!";
}

That about does it for the backend. For the front end / HTML side, I started by creating the page using jQuery and Material Design Components. I ended up bundling these up using webpack. This meant that I would need to deploy the files onto Google App Scripts. I did this by simply copy-and-paste the file contents. Thing to note is that on the Google App Script side, it only allows .gs and .html file types. So in order to bring in your own bundled .css and .js files, you need to create them inside the .html file. Below is an example of these files I created on Google side:

bundle.css.html
<style>
/** paste contents here
</style>

bundle.js.html
<script>
// paste contents here
</script>

Lastly, on the index.html file that will serve as the user entry page, we need to reference these files with template tags like the following:

 <?!= include('bundle.css'); ?>

The “include” is a function name I created. On the Code.gs backend file we need to implement that function and have it serve up the bundle.css.html and bundle.js.html files created above. This function actually serves up whatever file name is passed into it.

function include(filename) {
   return HtmlService.createTemplateFromFile(filename).getRawContent();
}

Some things to note – when deploying both web apps I found that the latest revision does not get deployed when reusing the same Project Version number. It is important to remember to updating the project version number when new changes need to be deployed. The URL does not change, but unless that version number is set to “new”, your latest changes will not get deployed.

Also, remember to set the app access. If the app is made available to the public, like shown above, then whatever changes it does on the backend (ex changing data on spreadsheets) will be done using the account set on the “Execute the app as”. Both this field and the “Who has access to the app” field can be configured such that it uses the credentials of the user accessing the app. But in this case, we need to make sure they have access to the backend file on Google Drive (share the spreadsheet file to that user).